﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using Ooaavee.Data.Model;

namespace Ooaavee.Data
{
    public static class RelationalModelLoader
    {
        /// <summary>
        /// Loads a relational model from the database.
        /// </summary>
        /// <param name="source">The source of the relational model.</param>
        /// <returns>The relational model of the database.</returns>
        public static RelationalModel Load(RelationalModelSource source)
        {
            if (source == null)
            {
                throw new ArgumentNullException("source");
            }

            RelationalModel model = new RelationalModel();
           
            Connections connections = new Connections(source);

            TableMap tables = new TableMap();

            Dictionary<string, DataType> dataTypes = new Dictionary<string, DataType>();

            using (OleDbConnection con = connections.GetOleDbConnection())
            {
                model.DatabaseName = con.Database;

                foreach (DataRow row in con.GetSchema("DataTypes").Rows)
                {
                    var providerDbType = row["ProviderDbType"].ToString();
                    var typeName = row["TypeName"].ToString();
                    var dataType = row["DataType"].ToString();
                    
                    DataType type = new DataType
                    {
                        ProviderDbType = Convert.ToInt32(providerDbType),
                        Name = typeName,
                        FrameworkType = dataType,
                        OleDbType = (OleDbType)Convert.ToInt32(providerDbType)
                    };

                    if (dataTypes.ContainsKey(providerDbType) == false)
                    {
                        dataTypes.Add(providerDbType, type);
                    }
                }

                foreach (DataRow row in con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new string[] { null, null, null }).Rows)
                {
                    var tableCatalog = row["TABLE_CATALOG"].ToString();
                    var tableSchema = row["TABLE_SCHEMA"].ToString();
                    var tableName = row["TABLE_NAME"].ToString();
                    var tableType = row["TABLE_TYPE"].ToString();

                    Table table = new Table
                    {
                        Parent = model,
                        Catalog = tableCatalog,
                        Schema = tableSchema,
                        Name = tableName,
                        TableType = tableType
                    };
                    
                    tables.Add(table);
                    model.Tables.Add(table);
                }

                foreach (DataRow row in con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new string[] { null, null, null }).Rows)
                {
                    var tableCatalog = row["TABLE_CATALOG"].ToString();
                    var tableSchema = row["TABLE_SCHEMA"].ToString();
                    var tableName = row["TABLE_NAME"].ToString();
                    var columnName = row["COLUMN_NAME"].ToString();
                    var ordinalPosition = row["ORDINAL_POSITION"].ToString();
                    var isNullable = row["IS_NULLABLE"].ToString();
                    var dataType = row["DATA_TYPE"].ToString();
                    var characterMaximumLength = row["CHARACTER_MAXIMUM_LENGTH"].ToString();
                    var numericPrecision = row["NUMERIC_PRECISION"].ToString();
                    var numericScale = row["NUMERIC_SCALE"].ToString();

                    Table table = tables.Search(tableCatalog, tableSchema, tableName);

                    Column column = new Column();

                    column.Parent = table;

                    column.Name = columnName;

                    column.OrdinalPosition = Convert.ToInt32(ordinalPosition);

                    column.IsNullable = Convert.ToBoolean(isNullable);

                    column.DataType = dataTypes[dataType];

                    if (characterMaximumLength.Length > 0)
                    {
                        column.CharacterMaximumLength = Convert.ToInt32(characterMaximumLength);
                    }

                    if (numericPrecision.Length > 0)
                    {
                        column.NumericPrecision = Convert.ToInt32(numericPrecision);
                    }

                    if (numericScale.Length > 0)
                    {
                        column.NumericScale = Convert.ToInt32(numericScale);
                    }

                    table.Columns.Add(column);
                }

                foreach (DataRow row in con.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, new string[] { null, null, null }).Rows)
                {
                    var tableCatalog = row["TABLE_CATALOG"].ToString();
                    var tableSchema = row["TABLE_SCHEMA"].ToString();
                    var tableName = row["TABLE_NAME"].ToString();
                    var columnName = row["COLUMN_NAME"].ToString();

                    Table table = tables.Search(tableCatalog, tableSchema, tableName);

                    Column column = table.Columns[columnName];
                    column.IsInPrimaryKey = true;
                }

                foreach (DataRow row in con.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, null).Rows)
                {
                    var pkTableCatalog = row["PK_TABLE_CATALOG"].ToString();
                    var pkTableSchema = row["PK_TABLE_SCHEMA"].ToString();
                    var pkTableName = row["PK_TABLE_NAME"].ToString();
                    var pkColumnName = row["PK_COLUMN_NAME"].ToString();
                    var fkTableCatalog = row["FK_TABLE_CATALOG"].ToString();
                    var fkTableSchema = row["FK_TABLE_SCHEMA"].ToString();
                    var fkTableName = row["FK_TABLE_NAME"].ToString();
                    var fkColumnName = row["FK_COLUMN_NAME"].ToString();

                    Table pkTable = tables.Search(pkTableCatalog, pkTableSchema, pkTableName);
                    Table fkTable = tables.Search(fkTableCatalog, fkTableSchema, fkTableName);

                    Column primaryKeyColumn = pkTable.Columns[pkColumnName];
                    Column foreignKeyColumn = fkTable.Columns[fkColumnName];


                    if (foreignKeyColumn.LinkedToColumns.Count > 0)
                    {
                        System.Diagnostics.Debugger.Break();
                    }

                    foreignKeyColumn.LinkedToColumns.Add(primaryKeyColumn);
                    primaryKeyColumn.LinkedByColumns.Add(foreignKeyColumn);
                }
            }

            return model;
        }

        #region TableMap

        private sealed class TableMap
        {
            private readonly Dictionary<string, Dictionary<string, Dictionary<string, Table>>> _map = new Dictionary<string, Dictionary<string, Dictionary<string, Table>>>();

            public void Add(Table table)
            {
                if (_map.ContainsKey(table.Catalog) == false)
                {
                    _map.Add(table.Catalog, new Dictionary<string, Dictionary<string, Table>>());
                }

                if (_map[table.Catalog].ContainsKey(table.Schema) == false)
                {
                    _map[table.Catalog].Add(table.Schema, new Dictionary<string, Table>());
                }

                _map[table.Catalog][table.Schema].Add(table.Name, table);
            }

            public Table Search(string catalog, string schema, string name)
            {
                if (_map.ContainsKey(catalog) == false)
                {
                    return null;
                }

                if (_map[catalog].ContainsKey(schema) == false)
                {
                    return null;
                }

                return _map[catalog][schema][name];
            }
        }

        #endregion
    }
}
